Simple Rules to Remember When Working with Indexes

SAS users are always interested in learning techniques related to improving data access. One way of improving information retrieval from a SAS data set or table is to create and use an index. An index consists of one or more columns that are used to uniquely identify each row within a table. Functioning as a SAS object containing the values in one or more columns in a table, an index may be defined as numeric, character, or a combination of both. This presentation will emphasize the rules that users should know when creating and using indexes to make information retrieval more efficient. INTRODUCTION Given the large number of books and articles on SQL and SQL-related topics, how strange it is not to find more material related to indexes and their impact on WHERE clause processing. Certainly, these topics deserve additional attention in order to assist SQL users’ achieve a greater understanding in applying these powerful features in their database applications. Indexes are designed to improve the speed in which subsets of data are accessed. Rather than physically sorting a table (as performed by the ORDER BY clause or the BY statement in PROC SORT), an index is designed to set up a logical data arrangement without the need to physically sort it. This has the advantage of reducing CPU and memory requirements. It also reduces data access time when using WHERE clause processing. This paper presents elements essential to achieving a better understanding of indexes and their effect on WHERE clause processing. TABLES USED IN EXAMPLES The data used in all the examples in this paper consists of a selection of movies that I’ve viewed over the years, along with its actors. The Movies table consists of six columns: title, length, category, year, studio, and rating. Title, category, studio, and rating are defined as character columns with length and year being defined as numeric columns. The data stored in the Movies table is illustrated below. MOVIES Table Coders' Corner SAS Global Forum 2010